﻿<%@ Page 
Language="C#" 
Buffer="false"
%>

<%@ Import Namespace="System.Text" %>
<%@ Import Namespace="System.Xml" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Net" %>
<%@ Import Namespace="System.IO" %>

<script type="text/c#" language="c#" runat="server">

string ConnStr = GetAppSetting("DB04Connect");

protected override void OnPreInit(EventArgs e){
    // Enabled trace in code
    this.Context.Trace.IsEnabled = false;
}

protected void Page_Load(object sender, EventArgs e){
	XmlDocument xReturn = getXML();
	Response.ContentType = "text/xml";
	Response.Write(xReturn.OuterXml);
}

public XmlDocument getXML()
{
    XmlDocument xReturn = new XmlDocument();

    DataTable tbl = new DataTable("tbl");
    Exception err = GetTbl(ref tbl, ConnStr);

    if (err == null)
    {
        tbl.Columns[0].ColumnName = "ID";
        tbl.Columns[0].Caption = "ID";
        tbl.Columns[1].ColumnName = "Name";
        tbl.Columns[1].Caption = "Name";
        tbl.Columns[2].ColumnName = "Region";
        tbl.Columns[2].Caption = "Region";
    }
    else
    {
        tbl.Columns.Add("ID", typeof(Int32));
        tbl.Columns[0].Caption = "ID";
        tbl.Columns.Add("Name", typeof(string));
        tbl.Columns[1].Caption = "Name";
        tbl.Columns.Add("Region", typeof(string));
        tbl.Columns[2].Caption = "Region";

        DataRow r = tbl.NewRow();
        r[0] = -1000;
        r[1] = "Ошибка";
        r[1] = "Ошибка получения данных.";
        tbl.Rows.Add(r);
    }

    using (StringWriter stringWriter = new StringWriter())
    {
        using (XmlTextWriter xmlTextwriter = new XmlTextWriter(stringWriter))
        {
            tbl.WriteXml(xmlTextwriter, XmlWriteMode.WriteSchema);
            //XmlNode xRoot = xReturn.CreateElement("root");
            //xReturn.AppendChild(xRoot);
            xReturn.LoadXml(stringWriter.ToString());
        }
    }

    return xReturn;
}

Exception GetTbl(ref DataTable tbl, string parConnStr)
{
    Exception ret = null;
    if (tbl == null)
        tbl = new DataTable();

    string sql =
@"
SELECT dbo.vw_a_shops.ID, dbo.vw_a_shops.NAME, dbo.ShopRegion.Region 
FROM dbo.vw_a_shops LEFT OUTER JOIN 
dbo.ShopRegion ON dbo.vw_a_shops.NAME = dbo.ShopRegion.Shop 
ORDER BY dbo.vw_a_shops.NAME 
";
    
    try
    {
        using (OleDbConnection cn = new OleDbConnection(parConnStr))
        {
            using (OleDbCommand myCMD = new OleDbCommand(sql, cn))
            {
                myCMD.CommandType = CommandType.Text;
                cn.Open();

                using (OleDbDataAdapter adapter = new OleDbDataAdapter(myCMD))
                {
                    adapter.Fill(tbl);
                }
            }
            cn.Close();
        }
    }
    catch (Exception ex)
    {
        ret = new Exception(ex.Message, ex);
    }

    return ret;
}

static public string GetAppSetting(String mName)
{
    String vStr = null;
    string strPath =
@"C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config";
    XmlDocument xmlDoc = new XmlDocument();
    xmlDoc.Load(strPath);
    XmlNode node = xmlDoc.SelectSingleNode(@"/configuration/appSettings");
    foreach (XmlNode keynode in node.ChildNodes)
    {
        string keyStr = keynode.Attributes["key"].Value;
        if (keyStr == mName)
        {
            vStr = keynode.Attributes["value"].Value;
            break;
        }
    }

    if (String.IsNullOrEmpty(vStr))
        vStr = "";

    return vStr;
}

</script>

